package com.group6.exam.teacher.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.group6.exam.common.util.DBUtil;
import com.group6.exam.teacher.entity.ScoreCondition;
import com.group6.exam.teacher.entity.ScorePage;

public class ScoreDaoImpl implements IScoreDao {

	// 查询班级名
	@Override
	public String getClassNameById(String classId) {
		String sql = "SELECT grade_name FROM grade WHERE grade_id=?";

		List<Map<String, Object>> list = DBUtil.executeQuery(sql, classId);

		return list != null && list.size() > 0 ? list.get(0).get("grade_name")
				+ "" : null;
	}

	// 查询学生姓名
	@Override
	public String getStudentNameById(String studentId) {
		String sql = "SELECT student_name FROM studentinfo WHERE student_id=?";

		List<Map<String, Object>> list = DBUtil.executeQuery(sql, studentId);

		return list != null && list.size() > 0 ? list.get(0)
				.get("student_name") + "" : null;
	}

	// 查询科目名称
	@Override
	public String getSubjectNameById(String subjectId) {
		String sql = "SELECT subject_name FROM subject WHERE subject_id=?";

		List<Map<String, Object>> list = DBUtil.executeQuery(sql, subjectId);

		return list != null && list.size() > 0 ? list.get(0)
				.get("subject_name") + "" : null;
	}

	// 获取总记录数
	@Override
	public int getTotalRecords(ScoreCondition condition) {
		StringBuilder sb = new StringBuilder();
		List<Object> list = new ArrayList<Object>();

		sb.append("SELECT * FROM score ");

		String subjectId = condition.getSubjectId();
		String classId = condition.getClassId();
		String studentId = condition.getStudentId();
		if (subjectId != null) {
			sb.append("WHERE score_subject_id = ?");
			list.add(subjectId);
		}
		if (classId != null) {
			sb.append("WHERE score_grade_id = ?");
			list.add(classId);
		}
		if (studentId != null) {
			sb.append("WHERE score_student_id = ?");
		}

		Object[] parameters = list.toArray();
		return DBUtil.executeQuery(sb.toString(), parameters).size();
	}

	@Override
	public List<Map<String, Object>> getPagingByScorePage(ScorePage scorePage,
			ScoreCondition condition) {
		StringBuilder sb = new StringBuilder();
		List<Object> list = new ArrayList<Object>();

		sb.append("SELECT * FROM score ");

		String subjectId = condition.getSubjectId();
		String classId = condition.getClassId();
		String studentId = condition.getStudentId();
		if (subjectId != null) {
			sb.append("WHERE score_subject_id = ? ");
			list.add(subjectId);
		}
		if (classId != null) {
			sb.append("WHERE score_grade_id = ? ");
			list.add(classId);
		}
		if (studentId != null) {
			sb.append("WHERE score_student_id = ? ");
			list.add(studentId);
		}

		sb.append("LIMIT ?, ?");
		int index = (scorePage.getCurrentPage() - 1) * scorePage.getPageSize();
		int pageSize = scorePage.getPageSize();
		list.add(index);
		list.add(pageSize);
		Object[] parameters = list.toArray();
		return DBUtil.executeQuery(sb.toString(), parameters);
	}

	// 通过科目名称获取科目的id
	@Override
	public String getScoreBySubName(String subStr) {
		String sql = "SELECT subject_id FROM subject WHERE subject_name like ?";

		List<Map<String, Object>> subList = DBUtil.executeQuery(sql, subStr);

		return subList != null && subList.size() > 0 ? subList.get(0).get(
				"subject_id")
				+ "" : null;
	}

	// 通过班级名称获取班级id
	@Override
	public String getScoreByClaName(String claStr) {
		String sql = "SELECT grade_id FROM grade WHERE grade_name like ?";

		List<Map<String, Object>> claList = DBUtil.executeQuery(sql, claStr);

		return claList != null && claList.size() > 0 ? claList.get(0).get(
				"grade_id")
				+ "" : null;
	}

	// 通过姓名获取学生id
	@Override
	public String getScoreByStuName(String stuStr) {
		String sql = "SELECT student_id FROM studentinfo WHERE student_name like ?";

		List<Map<String, Object>> stuList = DBUtil.executeQuery(sql, stuStr);

		return stuList != null && stuList.size() > 0 ? stuList.get(0).get(
				"student_id")
				+ "" : null;
	}

}
